﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace QUANLIBANHANG.DAL
{
    public class ConnectionSQL
    {
        static string[] line = File.ReadAllLines("./th.txt");// Lấy tên server trong file lưu lại
        public static SqlConnection conn = new SqlConnection(@"Data Source="+line[2]+";Initial Catalog=QUANLIBANHANG;Integrated Security=True");
        private static SqlCommand command;
        private static SqlDataAdapter adapter;
        // Hàm thực hiện câu lệnh insert, update, delete với CSDL
        public static void commandSQL(string sql)
        {
            try
            {
                conn.Open();
                command = new SqlCommand(sql,conn);
                command.CommandType = CommandType.Text;
                command.ExecuteNonQuery();
                conn.Close();

            }
            catch (SqlException ex)
            {

                throw ex;

            }
            finally
            {
                if (conn.State==ConnectionState.Open)
                {
                    conn.Close();
                }

            }
        }
        // Hàm thực hiện lấy về datatable thì thực hiện câu lênh select
        public static DataTable getTable(string dk)
        {
            DataTable dt = new DataTable();
            try
            {
                conn.Open();
                SqlDataAdapter da = new SqlDataAdapter(dk, conn);
                da.Fill(dt);
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return dt;
        }


        public static void executeQuery(string sqlProcedure)
        {
            try
            {
                conn.Open();
                command = new SqlCommand(sqlProcedure, conn);
                command.CommandType = CommandType.StoredProcedure;
                command.ExecuteNonQuery();
                conn.Close();

            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
 
            }
            
        }
        public static int executeQueryParameter(string sqlProcedure, List<SqlParameter> listparamemter)
        {
            
            try
            {
                conn.Open();
                command = new SqlCommand(sqlProcedure, conn);
                command.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter para in listparamemter)
                {
                    command.Parameters.Add(para);
                }
                conn.Close();
                return  command.ExecuteNonQuery();
                

            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

        
        public static DataTable getTablePro(string sqlString)
        {
            DataTable dt = new DataTable();
            adapter = new SqlDataAdapter(sqlString, conn);
            adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
            adapter.Fill(dt);
            return dt;
        }
        public static DataTable getTablePro(string sqlString,List<SqlParameter> listparameter)
        {
            DataTable dt = new DataTable();
            command = new SqlCommand(sqlString, conn);
            command.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter item in listparameter)
            {
                command.Parameters.Add(item);
            }
            adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
            adapter.Fill(dt);
            return dt;
        }
        
    }
}
